Deskripsi

Manajemen data infeksi dan vaksinasi Covid-19 seluruh negara.

Input

  1. Infected Case

    Sumber: Center for Systems Science and Engineering (CSSE) at Johns Hopkins University (GitHub)

    URL: https://github.com/CSSEGISandData/COVID-19

    Terdiri dari tiga data set:

    • Confirmed
    • Recovered
    • Death
  2. Population dan Kode Negara

    Sumber: Center for Systems Science and Engineering (CSSE) at Johns Hopkins University (GitHub)

    URL: https://github.com/CSSEGISandData/COVID-19

  3. Histori Vaksin

    Sumber: Our World in Data (Github)

    URL: https://github.com/owid/covid-19-data

  4. Vaccine manufacture

    Sumber: Our World in Data (Github)

    URL: https://github.com/owid/covid-19-data

Output

  1. Data historis (total akumulatif harian) kasus infeksi dan vaksinasi
  2. Data terkini dari kasus infeksi dan vaksinasi

Tools

  1. Package

    • base
    • tidyverse (dplyr, readr, lubridate)
  2. Function

    • read_csv
    • select
    • rename
    • arrange
    • group_by
    • mutate
    • summarise
    • left_join
    • pivot_wider
    • pivot_longer

library(tidyverse)
library(lubridate)

Infected Case

Sumber data:

Input:

Output:

infected <- function(url){
  read_csv(url) %>%
  rename_all(make.names) %>%
  pivot_longer(cols = starts_with("X"),
                 names_to = "last_update",
                 values_to = "total") %>%
  mutate(last_update = mdy(sub("X","", last_update))) %>%
  rename(country = Country.Region) %>%
  group_by(country, last_update) %>%
  summarise(total = sum(total)) %>%
  ungroup()
}
url_confirmed <- "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv"
url_recovered <- "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv"
url_death <-     "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv"

confirmed <- read_csv(url_confirmed)
confirmed
# join all together
infected_case <- infected(url_confirmed) %>% 
  left_join(infected(url_recovered), 
            by = c("country", "last_update")) %>%
  left_join(infected(url_death)
            , by = c("country", "last_update")) %>%
  rename(total_confirmed = total.x,
         total_recovered = total.y,
         total_death     = total) %>%
  mutate(total_active    = total_confirmed - (total_recovered + total_death)) %>%
  mutate(across(starts_with("total"), ~replace_na(., 0)))

infected_case

Populasi dan kode negara

Sumber data:

Input:

Output:

url <- "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/UID_ISO_FIPS_LookUp_Table.csv"
country <- read_csv(url)
country
country <- country %>%
  filter(UID == code3) %>%
  select (iso_code = iso3,
          country = Country_Region,
          latitude = Lat,
          longitude = Long_,
          population = Population)

country

Historis Vaksin

Sumber data:

Input:

Output:

url <- "https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/vaccinations/vaccinations.csv"
vaccination <- read_csv(url)
vaccination
vaccination <- vaccination %>%
  select(location:total_boosters) %>%
  mutate(across(total_vaccinations:total_boosters, 
                ~na_if(., 0))) %>%
  group_by(location, iso_code) %>%
  arrange(location, iso_code, date) %>%
  fill(total_vaccinations:total_boosters, 
       .direction = "down") %>%
  ungroup() %>%
  mutate(across(total_vaccinations:total_boosters, 
                ~replace_na(., 0))) %>%
  rename(country = location, last_update = date)

vaccination

Vaccine manufacture

Sumber data:

Input/Output:

url <- "https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/vaccinations/locations.csv"
vaccine_manufacture <- read_csv(url)
vaccine_manufacture

Data Historis Infected Case VS Vaccination

Input:

Ouput:

c19_daily <- infected_case %>%
  left_join(country, by = "country") %>%
  select(iso_code, country, population, last_update,
         starts_with("total")) %>%
  left_join(vaccination, by = c("iso_code", "last_update")) %>%
  group_by(iso_code) %>%
  arrange(iso_code, last_update) %>%
  fill(total_vaccinations:total_boosters, 
       .direction = "down") %>%
  ungroup() %>%
  mutate(across(total_vaccinations:total_boosters, 
                ~replace_na(., 0))) %>%
  rename(country = country.x) %>%
  select(-country.y)

c19_daily

Data Terkini

Input:

Ouput:

c19_latest <- c19_daily %>%
  # get latest data
  group_by(iso_code, country) %>%
  mutate(latest_date = max(last_update)) %>%
  ungroup() %>%
  filter(latest_date == last_update) %>%
  # get first_event
  left_join(
  c19_daily %>%
    pivot_longer(c(total_confirmed, total_death, total_vaccinations),
                   names_to = "grp",
                   values_to = "total") %>%
    filter(total > 0) %>%
    group_by(iso_code, grp) %>%
    summarise(last_update = min(last_update)) %>%
    ungroup() %>%
    pivot_wider(names_from = grp, values_from = last_update) %>%
    rename(first_confirmed = total_confirmed,
           first_death = total_death,
           first_vaccination = total_vaccinations)) %>%
  # vaccine manufacture
  left_join(vaccine_manufacture, by = "iso_code") %>%
  select(iso_code:last_update, 
         first_confirmed, first_death, 
         total_confirmed:total_active,
         first_vaccination,
         total_vaccinations:total_boosters,
         vaccines_manufacture = vaccines)

c19_latest